Generated code - Transactions, Adapter
Preface
Adapter supports ADO.NET transactions and System.Transactions (distributed)
transactions. This section explains how ADO.NET transactions and distributed
transactions are used in Adapter. Adapter automatically uses ADO.NET
transactions for recursive saves and save/delete actions for collections of
entities so you don't have to start/commit transactions yourself if you
persist entity changes.
Normal, native database transactions
Native database transactions are provided by ADO.NET; a transaction started
using ADO.NET starts a native database transaction. LLBLGen Pro's native database transactions are implemented in the
DataAccessAdapter object. As the transactional code is inside the
DataAccessAdapter
class,
every method of the
DataAccessAdapter object you call after a transaction is
started on the
DataAccessAdapter class
(implicitly or
explicitly), is ran inside that transaction, including stored procedure
calls, entity fetches, entity collection saves etc.
You don't have to
add entity objects or entity collection objects to the
DataAccessAdapter
object to make them participate in the transaction, just call a
DataAccessAdapter
method and
it's inside the transaction of that particular
DataAccessAdapter object. If you wish to run a particular action outside of a transaction, create a new
DataAccessAdapter object for that particular action. You can
start/commit multiple transactions after each other on the same
DataAccessAdapter instance.
Note:
|
If you start a new transaction by calling DataAccessAdapter.StartTransaction() the connection is kept open until Rollback() or Commit() is called.
|
Persisting entities through a
DataAccessAdapter class instance by
recursively saving an entity graph or by saving an entity collection will
automatically start a transaction as multiple entities are involved. This
will always be a transaction with isolation level ReadCommitted. This frees
you in general from transaction management.
You can also start a
transaction explicitly. The example below will help illustrate starting a transaction
explicitly. The example below will update two different entities in one transaction.
For this example, it has to be done in one atomic action, and therefore
requires a transaction.
// [C#]
// create adapter for fetching and the transaction.
DataAccessAdapter adapter = new DataAccessAdapter();
// start the transaction explicitly.
adapter.StartTransaction(IsolationLevel.ReadCommitted, "TwoUpates");
try
{
// fetch the two entities
CustomerEntity customer = new CustomerEntity("CHOPS");
OrderEntity order = new OrderEntity(10254);
adapter.FetchEntity(customer);
adapter.FetchEntity(order);
// alter the entities
customer.Fax = "12345678";
order.Freight = 12;
// save the two entities again
adapter.SaveEntity(customer);
adapter.SaveEntity(order);
// done
adapter.Commit();
}
catch
{
// abort, roll back the transaction
adapter.Rollback();
// bubble up exception
throw;
}
finally
{
// clean up. Necessary action.
adapter.Dispose();
}
' [VB.NET]
' create adapter for fetching and the transaction.
Dim adapter As New DataAccessAdapter()
' start the transaction.
adapter.StartTransaction(IsolationLevel.ReadCommitted, "TwoUpates")
Try
' fetch the two entities
Dim customer As New CustomerEntity("CHOPS")
Dim order As New OrderEntity(10254)
adapter.FetchEntity(customer)
adapter.FetchEntity(order)
' alter the entities
customer.Fax = "12345678"
order.Freight = 12
' save the two entities again.
adapter.SaveEntity(customer)
adapter.SaveEntity(order)
' done
adapter.Commit()
Catch
' abort, roll back the transaction
adapter.Rollback()
' bubble up exception
Throw
Finally
' clean up. Necessary action.
adapter.Dispose()
End Try
First a
DataAccessAdapter object is created and a transaction is
explicitly started. As soon as you start the transaction, a database
connection is open and usable.
It's best practice to embed the usage of a transaction in a try/catch/finally statement as it is done in the example above,
or a
using block.
This ensures that if something fails during the usage of the transaction, everything is rolled back or, at the end, everything is committed correctly.
A
DataAccessAdapter instance will rollback an open transaction when
it's disposed.
Transaction save-points
Most databases support transaction save-points. Transaction save-points make it possible to do fine grained transaction control on a semi-nested level. This can
be helpful as ADO.NET doesn't support nested transactions. Save-points let you define a point in a transaction to which you can roll back, without rolling back
the complete transaction. This can be handy if you for example have saved some entities in a transaction which were saved OK, and another one fails, however the failure of that
save shouldn't terminate the whole transaction, just roll back the transaction to a given point in the transaction.
The following example illustrates the save-point functionality. It first saves a new
Address entity and after that it saves the
transaction. It then saves a new
Customer entity but takes into account that this can fail. If it does, it should roll back to the save-point set
before the second save, so it avoids rolling back the complete transaction.
//C#
DataAccessAdapter adapter = new DataAccessAdapter();
try
{
adapter.StartTransaction(IsolationLevel.ReadCommitted, "SavepointRollback");
// first save a new address
AddressEntity newAddress = new AddressEntity();
// ... fill the address entity with values
// save it.
adapter.SaveEntity(newAddress, true);
// create a transaction save point
adapter.SaveTransaction("SavepointAddress");
// save a new customer
CustomerEntity newCustomer = new CustomerEntity();
// ... fill the customer entity with values
newCustomer.VisitingAddress = newAddress;
newCustomer.BillingAddress = newAddress;
try
{
adapter.SaveEntity(newCustomer, true);
}
catch(Exception ex)
{
// something was wrong.
// ... handle ex here.
// roll back to savepoint.
adapter.Rollback("SavepointAddress");
}
// commit the transaction. If the customer save failed,
// only address is saved, otherwise both.
adapter.Commit();
}
catch
{
// fatal error, roll back everything
adapter.Rollback();
throw;
}
finally
{
adapter.Dispose();
}
' VB.NET
Dim adapter As new DataAccessAdapter()
Try
adapter.StartTransaction(IsolationLevel.ReadCommitted, "SavepointRollback")
' first save a new address
Dim newAddress As New AddressEntity()
' ... fill the address entity with values
' save it.
adapter.SaveEntity(newAddress, True)
' save the transaction
adapter.SaveTransaction("SavepointAddress")
' save a new customer
Dim newCustomer As New CustomerEntity()
' ... fill the customer entity with values
newCustomer.VisitingAddress = newAddress
newCustomer.BillingAddress = newAddress
Try
adapter.SaveEntity(newCustomer, True)
Catch(Exception ex)
' something was wrong.
' ... handle ex here.
' roll back to savepoint.
adapter.Rollback("SavepointAddress")
End Try
' commit the transaction. If the customer save failed,
' only address is saved, otherwise both.
adapter.Commit()
Catch
// fatal error, roll back everything
adapter.Rollback()
Throw
Finally
adapter.Dispose()
End Try
Note:
|
Microsoft Access and Microsoft's Oracle ADO.NET provider don't support savepoints in transactions, so this feature is not supported when you use
LLBLGen Pro with MS Access or when you use the MS Oracle provider with Oracle. In the case of Oracle, use ODP.NET instead, which does support
save points.
|
System.Transactions support
.NET contains the System.Transactions namespace, which is a namespace with the
TransactionScope class, which eases the creation of distributed transactions,
by specifying a given scope. All transactions, e.g. ADO.NET transactions, are automatically elevated to distributed transactions, if required by the
TransactionScope they're declared in. This requires support by the used database system as the database system has to be able to
promote
a non-distributed transaction to a distributed transaction.
The developer can define such a
TransactionScope using the normal .NET constructs, like
using(TransactionScope scope = new TransactionScope())
{
// your code here.
}
A
DataAccessAdapter object is able to determine if it's participating inside an ambient transaction of
System.Transactions. If so, it enlists itself with a Resource Manager
tied to the System.Transactions transaction. As soon as a
Transaction or
DataAccessAdapter is enlisted through a Resource Manager, the Commit() and Rollback() methods
are setting the
ResourceManager's commit/abort signal which is requested by the System.Transactions' Transaction manager. If multiple transactions are executed
on a
DataAccessAdapter and one rolled back, the resource manager will
report an abort. Once one rollback is requested, the transaction will always report a rollback to the MSDTC.
Going out of scope
When the System.Transactions transaction is committed or rolled back, the Resource manager is notified and will then notify the
DataAccessAdapter that it can commit/rollback the transaction. That call will then notify the enlisted entities of the outcome of the transaction.
Multiple transactions executed using a single DataAccessAdapter object
For the DataAccessAdapter it will look like its still inside the same transaction, so no new transaction is started. This will make sure that an entity which
is already participating in the transaction isn't enlisted again and the field values aren't saved again etc.
Example
Below is an example which shows the usage of a TransactionScope in combination of a DataAccessAdapter object. The code contains Assert statements to
illustrate the state / outcome of the various statements.
// C#
CustomerEntity newCustomer = new CustomerEntity();
// fill newCustomer's fields.
// ..
AddressEntity newAddress = new AddressEntity();
// fill newAddress' fields.
// ..
// start the scope.
using( TransactionScope ts = new TransactionScope() )
{
// as we're inside the transaction scope, we can now create a DataAccessAdapter object and
// start a connection + transaction. The connection + transaction will be enlisted through a
// resource manager in the TransactionScope ts and will be controlled by that TransactionScope.
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
// save 2 entities, recursively. This should be done in one
// transaction, namely the transaction scope we've started.
newCustomer.VisitingAddress = newAddress;
newCustomer.BillingAddress = newAddress;
Assert.IsTrue( adapter.SaveEntity( newCustomer, true) );
// save went well, alter the entities, which are fetched back, and
// save again.
newCustomer.CompanyEmailAddress += " ";
newAddress.StreetName += " ";
Assert.IsTrue( adapter.SaveEntity( newCustomer, true ) );
}
// do not call Complete, as we want to rollback the transaction and see if the rollback indeed succeeds.
// as the TransactionScope goes out of scope, the on-going transaction is rolled back.
}
// at this point the transaction of the previous using block is rolled back.
// let the DTC and the system.transactions threads deal with the objects.
// this sleep is only needed because we're going to access the data directly after the rollback. In normal code,
// this sleep isn't necessary.
Thread.Sleep( 1000 );
// test if the data is still there. Shouldn't be as the transaction has been rolled back.
using( DataAccessAdapter adapter = new DataAccessAdapter() )
{
CustomerEntity fetchedCustomer = new CustomerEntity( newCustomer.CustomerId );
Assert.IsFalse( adapter.FetchEntity( fetchedCustomer ) );
AddressEntity fetchedAddress = new AddressEntity( newAddress.AddressId );
Assert.IsFalse( adapter.FetchEntity( fetchedAddress ) );
Assert.AreEqual( 0, newAddress.AddressId );
}
' VB.NET
Dim NewCustomer As New CustomerEntity()
' fill NewCustomer's fields.
' ..
Dim NewAddress As New AddressEntity()
' fill NewAddress' fields.
' ..
' start the scope.
Using ts As New TransactionScope()
' as we're inside the transaction scope, we can now create a DataAccessAdapter object and
' start a connection + transaction. The connection + transaction will be enlisted through a
' resource manager in the TransactionScope ts and will be controlled by that TransactionScope.
Using adapter As New DataAccessAdapter()
' save 2 entities, recursively. This should be done in one
' transaction, namely the transaction scope we've started.
NewCustomer.VisitingAddress = NewAddress
NewCustomer.BillingAddress = NewAddress
Assert.IsTrue( adapter.SaveEntity( NewCustomer, True) )
' save went well, alter the entities, which are fetched back, and
' save again.
NewCustomer.CompanyEmailAddress = NewCustomer.CompanyEmailAddress & " "
NewAddress.StreetName = NewAddress.StreetName & " "
Assert.IsTrue( adapter.SaveEntity( NewCustomer, True ) )
End Using
' do not call Complete, as we want to rollback the transaction and see if the rollback indeed succeeds.
' as the TransactionScope goes out of scope, the on-going transaction is rolled back.
End Using
' at this point the transaction of the previous using block is rolled back.
' let the DTC and the system.transactions threads deal with the objects.
' this sleep is only needed because we're going to access the data directly after the rollback. In normal code,
' this sleep isn't necessary.
Thread.Sleep( 1000 )
' test if the data is still there. Shouldn't be as the transaction has been rolled back.
Using adapter As New DataAccessAdapter()
Dim fetchedCustomer As New CustomerEntity( NewCustomer.CustomerId )
Assert.IsFalse( adapter.FetchEntity( fetchedCustomer ) )
Dim fetchedAddress = New AddressEntity( NewAddress.AddressId )
Assert.IsFalse( adapter.FetchEntity( fetchedAddress ) )
Assert.AreEqual( 0, NewAddress.AddressId )
End Using